
use shq_common::prelude::get_ch_pool;
pub (super) async fn create_analysis_dept1(_rept_name: &str,params:&[(&str,&str)],tmp_table_name:&str) -> anyhow::Result<()> {
    let (_,date,_,col_name,_,level_cause,cyno_cause)=parse_params(params)?;
    let sql=format!(r#" create table {tmp_table_name} Engine=Memory as
        with toDate('{date}') AS CURDATE,dateSub(day, 1, CURDATE) AS SRDATE,
            dateSub(day,1,toStartOfYear(CURDATE)) AS SNDATE,dateSub(day,1,toStartOfMonth(CURDATE)) AS SYDATE,
            dictGet('DICT_USER_DEPT','PDID',EMID) AS PDID,dictGet('DICT_USER_DEPT','DPID',EMID) AS DPID,
            dictGet('DICT_USER_DEPT','PDNM',EMID) AS PDNM2
        select PDID,MAX(PDNM2) as PDNM,sumIf({col_name},DTID=CURDATE) as ACBL,sumIf({col_name},DTID=SRDATE) as BSR,
            sumIf({col_name},DTID=SYDATE) as BSY,sumIf({col_name},DTID=SNDATE) as BSN
        from DFKB_EMPL_INDEX
        where DTID in (CURDATE,SRDATE,SYDATE,SNDATE) {cyno_cause} {level_cause}
        group by PDID
        order by ACBL DESC"#);
    let mut client = get_ch_pool().get_handle().await?;
    client.execute(sql).await?;
    Ok(())
}
pub (super) async fn create_analysis_dept2(_rept_name: &str,params:&[(&str,&str)],tmp_table_name:&str) -> anyhow::Result<()> {
    let (_,date,_,col_name,_,level_cause,cyno_cause)=parse_params(params)?;
    let sql=format!(r#" create table {tmp_table_name} Engine=Memory as
        with toDate('{date}') AS CURDATE,dateSub(day, 1, CURDATE) AS SRDATE,
            dateSub(day,1,toStartOfYear(CURDATE)) AS SNDATE,dateSub(day,1,toStartOfMonth(CURDATE)) AS SYDATE,
            dictGet('DICT_USER_DEPT','PDID',EMID) AS PDID,dictGet('DICT_USER_DEPT','DPID',EMID) AS DPID,
            dictGet('DICT_USER_DEPT','PDNM',EMID) AS PDNM2,dictGet('DICT_USER_DEPT','DPNM',EMID) AS DPNM2
        select DPID,MAX(PDNM2) as PDNM,MAX(DPNM2) as DPNM,sumIf({col_name},DTID=CURDATE) as ACBL,sumIf({col_name},DTID=SRDATE) as BSR,
            sumIf({col_name},DTID=SYDATE) as BSY,sumIf({col_name},DTID=SNDATE) as BSN
        from DFKB_EMPL_INDEX
        where DTID in (CURDATE,SRDATE,SYDATE,SNDATE) {cyno_cause} {level_cause}
        group by DPID
        order by ACBL DESC"#);
    let mut client = get_ch_pool().get_handle().await?;
    client.execute(sql).await?;
    Ok(())
}
pub (super) async fn create_analysis_empl(_rept_name: &str,params:&[(&str,&str)],tmp_table_name:&str) -> anyhow::Result<()> {
    let (_,date,_,col_name,_,level_cause,cyno_cause)=parse_params(params)?;
    let sql=format!(r#" create table {tmp_table_name} Engine=Memory as
        with toDate('{date}') AS CURDATE,dateSub(day, 1, CURDATE) AS SRDATE,
            dateSub(day,1,toStartOfYear(CURDATE)) AS SNDATE,dateSub(day,1,toStartOfMonth(CURDATE)) AS SYDATE,
            dictGet('DICT_USER_DEPT','PDID',EMID) AS PDID,dictGet('DICT_USER_DEPT','DPID',EMID) AS DPID,
            dictGet('DICT_USER_DEPT','PDNM',EMID) AS PDNM2,dictGet('DICT_USER_DEPT','DPNM',EMID) AS DPNM2,
            dictGet('DICT_USER_DEPT','EMNM',EMID) AS EMNM2
        select EMID,MAX(PDNM2) as PDNM,DPNM2 as DPNM,MAX(EMNM2) as EMNM,sumIf({col_name},DTID=CURDATE) as ACBL,sumIf({col_name},DTID=SRDATE) as BSR,
            sumIf({col_name},DTID=SYDATE) as BSY,sumIf({col_name},DTID=SNDATE) as BSN
        from DFKB_EMPL_INDEX
        where DTID in (CURDATE,SRDATE,SYDATE,SNDATE) {cyno_cause} {level_cause}
        group by EMID
        order by ACBL DESC"#);
    let mut client = get_ch_pool().get_handle().await?;
    client.execute(sql).await?;
    Ok(())
}
pub (super) async fn create_analysis_comp_clie(_rept_name: &str,params:&[(&str,&str)],tmp_table_name:&str) -> anyhow::Result<()> {
    let (_,date,_,col_name,_,level_cause,cyno_cause)=parse_params(params)?;
    let sql=format!(r#" create table {tmp_table_name} Engine=Memory as
        with toDate('{date}') AS CURDATE,dateSub(day, 1, CURDATE) AS SRDATE,
            dateSub(day,1,toStartOfYear(CURDATE)) AS SNDATE,dateSub(day,1,toStartOfMonth(CURDATE)) AS SYDATE,
            dictGet('DICT_USER_DEPT','PDID',EMID) AS PDID,dictGet('DICT_USER_DEPT','DPID',EMID) AS DPID,
            dictGet('DICT_COMP_CLIE','ENCD',CUID) AS ENCD2,dictGet('DICT_COMP_CLIE','CUNM',CUID) AS CUNM2
        select EMID,CUID,MAX(ENCD2) as ENCD,MAX(CUNM2) as CUNM,sumIf({col_name},DTID=CURDATE) as ACBL,sumIf({col_name},DTID=SRDATE) as BSR,
            sumIf({col_name},DTID=SYDATE) as BSY,sumIf({col_name},DTID=SNDATE) as BSN
        from DFKB_COMP_CLIE_INDEX
        where DTID in (CURDATE,SRDATE,SYDATE,SNDATE) {cyno_cause} {level_cause}
        group by CUID,EMID
        order by ACBL DESC"#);
    let mut client = get_ch_pool().get_handle().await?;
    client.execute(sql).await?;
    Ok(())
}
pub (super) async fn create_analysis_indi_clie(_rept_name: &str,params:&[(&str,&str)],tmp_table_name:&str) -> anyhow::Result<()> {
    let (_,date,_,col_name,_,level_cause,cyno_cause)=parse_params(params)?;
    let sql=format!(r#" create table {tmp_table_name} Engine=Memory as
        with toDate('{date}') AS CURDATE,dateSub(day, 1, CURDATE) AS SRDATE,
            dateSub(day,1,toStartOfYear(CURDATE)) AS SNDATE,dateSub(day,1,toStartOfMonth(CURDATE)) AS SYDATE,
            dictGet('DICT_USER_DEPT','PDID',EMID) AS PDID,dictGet('DICT_USER_DEPT','DPID',EMID) AS DPID,
            dictGet('DICT_INDI_CLIE','CUNO',CUID) AS CUNO2,dictGet('DICT_INDI_CLIE','CUNM',CUID) AS CUNM2
        select EMID,CUID,MAX(CUNO2) as CUNO,MAX(CUNM2) as CUNM,sumIf({col_name},DTID=CURDATE) as ACBL,sumIf({col_name},DTID=SRDATE) as BSR,
            sumIf({col_name},DTID=SYDATE) as BSY,sumIf({col_name},DTID=SNDATE) as BSN
        from DFKB_INDI_CLIE_INDEX
        where DTID in (CURDATE,SRDATE,SYDATE,SNDATE) {cyno_cause} {level_cause}
        group by EMID,CUID
        order by ACBL DESC"#);
    let mut client = get_ch_pool().get_handle().await?;
    client.execute(sql).await?;
    Ok(())
}
#[derive(Debug,Clone, PartialEq, Eq, PartialOrd, Ord,)]
pub (super) enum TimeLevel {
    Year,
    Quarter,
    Month,
    Day,
}
impl TimeLevel {
    pub (super) fn try_from_str(vstr:&str)->anyhow::Result<Self>{
        let v=vstr.to_lowercase();
        match v.as_str() {
            "year"=>Ok(TimeLevel::Year),
            "quarter"=>Ok(TimeLevel::Quarter),
            "month"=>Ok(TimeLevel::Month),
            "day"=>Ok(TimeLevel::Day),
            _=>anyhow::bail!("参数time_level:{vstr}无效，必须是year,quarter,month,day"),
        }
    }
}
#[derive(Debug,Clone, PartialEq, Eq, PartialOrd, Ord,)]
pub (super) enum DataLevel {
    Dept1,
    Dept2,
    User,
    CompClie,
    IndiClie,
}
impl DataLevel {
    pub (super) fn try_from_str(vstr:&str)->anyhow::Result<Self>{
        let v=vstr.to_lowercase();
        match v.as_str() {
            "dept1"=>Ok(DataLevel::Dept1),
            "dept2"=>Ok(DataLevel::Dept2),
            "user"=>Ok(DataLevel::User),
            "comp_clie"=>Ok(DataLevel::CompClie),
            "indi_clie"=>Ok(DataLevel::CompClie),
            _=>anyhow::bail!("参数empl_level:{vstr}无效，必须是DEPT1,DEPT2,USER,COMP_CLIE,INDI_CLIE")
        }
    }
}

#[derive(Debug, Clone, PartialEq, Eq, PartialOrd, Ord,)]
pub (super) enum EmplLevel {
    All,
    Dept1,
    Dept2,
    User
}
impl EmplLevel {
    pub (super) fn try_from_str(vstr:&str)->anyhow::Result<Self>{
        let v=vstr.to_lowercase();
        match v.as_str() {
            "all"=>Ok(EmplLevel::All),
            "dept1"=>Ok(EmplLevel::Dept1),
            "dept2"=>Ok(EmplLevel::Dept2),
            "user"=>Ok(EmplLevel::User),
            _=>anyhow::bail!("参数empl_level:{vstr}无效，必须是ALL,DEPT1,DEPT2,USER")
        }
    }
}

#[derive(Debug,Clone, PartialEq, Eq, PartialOrd, Ord,)]
pub (super) enum ItemType {
    TPNO,
    CYNO,
    ITID,
    PERD,
}
impl ItemType {
    pub (super) fn try_from_str(vstr:&str)->anyhow::Result<Self>{
        let v=vstr.to_uppercase();
        match v.as_str() {
            "TPNO"=>Ok(ItemType::TPNO),
            "CYNO"=>Ok(ItemType::CYNO),
            "ITID"=>Ok(ItemType::ITID),
            "PERD"=>Ok(ItemType::PERD),
            _=>anyhow::bail!("参数item_typr:{v}无效，必须是TPNO,CYNO,ITID,PERD")
        }
    }
}

pub (super) fn parse_params<'a>(params:&'a [(&'a str,&'a str)])->anyhow::Result<(&'a str,&'a str,Option<TimeLevel>,&'a str,Option<ItemType>,String,&'a str)>{
    let date=params.iter().find(|(k,_)|*k=="edate"||*k=="date").map(|(_,v)|*v).unwrap_or("");
    let sdate=if date.is_empty() {"19700101"} else{date};
    let date=params.iter().find(|(k,_)|*k=="edate"||*k=="date").map(|(_,v)|*v).unwrap_or("");
    let edate=if date.is_empty() {"19700101"} else{date};
    let cyno=params.iter().find(|(k,_)|*k=="cyno").map(|(_,v)|*v).unwrap_or("");
    let cyno=if cyno.is_empty() {"1"} else{cyno};
    let time_level=params.iter().find(|(k,_)|*k=="time_level").map(|(_,v)|*v).unwrap_or("");
    let time_level=if time_level.is_empty() {None} else{Some(TimeLevel::try_from_str(time_level)?)};
    let empl_level=params.iter().find(|(k,_)|*k=="empl_level").map(|(_,v)|*v).unwrap_or("");
    let empl_level=EmplLevel::try_from_str(empl_level)?;
    let empl_id=params.iter().find(|(k,_)|*k=="empl_id").map(|(_,v)|*v).unwrap_or("");
    let empl_id=if empl_id.is_empty() {"0"} else{empl_id};
    let mut empl_id:u64=empl_id.parse().unwrap_or(0);
    if empl_level==EmplLevel::All {
        empl_id=0;
    }
    let item_type=params.iter().find(|(k,_)|*k=="item_type").map(|(_,v)|*v).unwrap_or("");
    let item_type=if item_type.is_empty() {None} else{Some(ItemType::try_from_str(item_type)?)};
    let col_name=params.iter().find(|(k,_)|*k=="col_name").map(|(_,v)|*v).unwrap_or("");
    let col_name=if col_name.is_empty() {"ZBCB_GS"} else{col_name};
    let level_cause=match empl_level {
        EmplLevel::All => "".to_owned(),
        EmplLevel::Dept1 => format!("and EMID in (select EMID from DICT_USER_DEPT where PDID={empl_id})"),
        EmplLevel::Dept2 => format!("and EMID in (select EMID from DICT_USER_DEPT where PDID={empl_id})"),
        EmplLevel::User => format!("and EMID={empl_id}"),
    };
    let cyno_cause=if cyno=="1" {
        ""
    }else if cyno=="2" {
        "and CYNO='01'"
    }else if cyno=="3" {
        "and CYNO<>'01'"
    }else {
        anyhow::bail!("参数cyno:{cyno}无效,必须是1:本外币，2:人民币，3:外币折人民币")
    };
    Ok((sdate,edate,time_level,col_name,item_type,level_cause,cyno_cause))
}

